#!/usr/bin/env python
# -*- coding: utf-8 -*-

from flask import Flask, request, jsonify # type: ignore
from flask_cors import CORS
import pandas as pd
import pymysql
import datetime
import os
import tempfile
import traceback  # 添加调试输出
import sys  # 添加系统信息输出
from werkzeug.utils import secure_filename

print("===== 启动供应商API服务 =====")
print(f"Python版本: {sys.version}")
print(f"当前工作目录: {os.getcwd()}")

app = Flask(__name__)
# 简化CORS配置，允许所有来源
CORS(app, supports_credentials=True)

print("已配置CORS，允许所有来源")

# 数据库连接配置
db_config = {
    'host': 'localhost',
    'port': 3306,  # 明确指定端口
    'user': 'root',  # 数据库用户名
    'passwd': '123456',  # 密码
    'db': 'premade_food_trace',  # 数据库名
    'charset': 'utf8mb4'  # 添加字符集
}

print(f"数据库配置: {db_config}")

# 配置上传文件相关参数
UPLOAD_FOLDER = tempfile.gettempdir()
ALLOWED_EXTENSIONS = {'xlsx', 'xls'}
MAX_CONTENT_LENGTH = 10 * 1024 * 1024  # 10MB

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# 确保数据库和表存在
def init_database():
    conn = None
    try:
        print("尝试连接MySQL数据库...")
        # 连接MySQL，不指定数据库
        conn = pymysql.connect(
            host=db_config['host'],
            port=db_config['port'],
            user=db_config['user'],
            passwd=db_config['passwd'],
            charset=db_config['charset']
        )
        cursor = conn.cursor()
        
        # 创建数据库（如果不存在）
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_config['db']} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
        print(f"已创建或检查数据库 {db_config['db']}")
        
        # 使用数据库
        cursor.execute(f"USE {db_config['db']}")
        
        # 创建供应商表（如果不存在）
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS suppliers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL COMMENT '公司名称',
            code VARCHAR(50) COMMENT '简称',
            contact_person VARCHAR(50) COMMENT '联系人',
            phone VARCHAR(50) COMMENT '联系方式',
            address VARCHAR(200) COMMENT '总部地址',
            found_time DATE COMMENT '成立时间',
            license_number VARCHAR(50) COMMENT '许可证号',
            business_scope TEXT COMMENT '主营业务',
            status ENUM('active', 'inactive') DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        conn.commit()
        print("数据库初始化成功")
    except Exception as e:
        print(f"初始化数据库出错: {e}")
        print("详细错误信息:")
        traceback.print_exc()
    finally:
        if conn:
            conn.close()
            print("数据库连接已关闭")

# 获取数据库连接
def get_db_connection():
    try:
        print("尝试建立数据库连接...")
        conn = pymysql.connect(
            host=db_config['host'],
            port=db_config['port'],
            user=db_config['user'],
            passwd=db_config['passwd'],
            db=db_config['db'],
            charset=db_config['charset'],
            cursorclass=pymysql.cursors.DictCursor
        )
        print("数据库连接成功")
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        print("详细错误信息:")
        traceback.print_exc()
        raise

# 供应商列表API
@app.route('/api/suppliers', methods=['GET'])
def get_suppliers():
    try:
        print("=== 接收到获取供应商列表请求 ===")
        # 获取查询参数
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        keyword = request.args.get('keyword', '')
        supplier_type = request.args.get('type', '')
        
        print(f"查询参数: page={page}, pageSize={page_size}, keyword='{keyword}', type='{supplier_type}'")
        
        # 计算偏移量
        offset = (page - 1) * page_size
        
        print("尝试获取数据库连接...")
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 构建查询条件
        where_clause = "1=1"
        if keyword:
            where_clause += f" AND (name LIKE '%{keyword}%' OR code LIKE '%{keyword}%' OR business_scope LIKE '%{keyword}%')"
        if supplier_type and supplier_type != '全部':
            where_clause += f" AND business_scope LIKE '%{supplier_type}%'"
        
        print(f"SQL查询条件: {where_clause}")
        
        # 查询总数
        try:
            count_sql = f"SELECT COUNT(*) as total FROM suppliers WHERE {where_clause}"
            print(f"执行SQL: {count_sql}")
            cursor.execute(count_sql)
            total = cursor.fetchone()['total']
            print(f"总记录数: {total}")
        except Exception as e:
            print(f"查询总数失败: {e}")
            traceback.print_exc()
            raise
        
        # 查询数据
        try:
            data_sql = f"""
                SELECT 
                    id,
                    name,
                    code,
                    contact_person,
                    phone,
                    address,
                    found_time,
                    business_scope,
                    license_number,
                    status
                FROM suppliers 
                WHERE {where_clause}
                ORDER BY id DESC 
                LIMIT {offset}, {page_size}
            """
            print(f"执行SQL: {data_sql}")
            cursor.execute(data_sql)
            suppliers = cursor.fetchall()
            print(f"查询到 {len(suppliers)} 条记录")
            if suppliers and len(suppliers) > 0:
                print(f"第一条记录: {suppliers[0]}")
        except Exception as e:
            print(f"查询数据失败: {e}")
            traceback.print_exc()
            raise
        
        # 关闭连接
        conn.close()
        print("数据库连接已关闭")
        
        # 创建响应对象
        print("创建响应...")
        response = jsonify({
            'status': 'success',
            'message': '获取成功',
            'total': total,
            'items': suppliers
        })
        
        # 添加缓存控制头，确保前端获取最新数据
        response.headers['Cache-Control'] = 'no-cache, no-store, must-revalidate'
        response.headers['Pragma'] = 'no-cache'
        response.headers['Expires'] = '0'
        
        print("响应准备完成，返回数据")
        return response
    except Exception as e:
        error_msg = f"获取供应商列表失败: {str(e)}"
        print(error_msg)
        print("详细错误信息:")
        traceback.print_exc()
        return jsonify({
            'status': 'error',
            'message': error_msg
        }), 500

# 导入Excel文件处理
@app.route('/api/suppliers/import', methods=['POST', 'OPTIONS'])
def import_suppliers():
    if request.method == 'OPTIONS':
        return '', 200
        
    if 'file' not in request.files:
        print("未找到上传文件")
        return jsonify({
            'status': 'error',
            'message': '未找到上传文件'
        }), 400
        
    file = request.files['file']
    if file.filename == '':
        print("未选择文件")
        return jsonify({
            'status': 'error',
            'message': '未选择文件'
        }), 400
        
    if not allowed_file(file.filename):
        print(f"不支持的文件类型: {file.filename}")
        return jsonify({
            'status': 'error',
            'message': '仅支持.xlsx和.xls格式的Excel文件'
        }), 400
    
    temp_file_path = None
    try:
        # 生成安全的文件名
        filename = secure_filename(file.filename)
        temp_file_path = os.path.join(UPLOAD_FOLDER, filename)
        print(f"保存临时文件: {temp_file_path}")
        
        # 保存临时文件
        file.save(temp_file_path)
        
        # 读取Excel文件
        print("开始读取Excel文件")
        df = pd.read_excel(temp_file_path)
        print(f"Excel列名: {df.columns.tolist()}")

        # 新增：转换列名为字符串类型（解决列名含不可见字符的问题）
        df.columns = df.columns.astype(str)
        
        # 检查所需列是否存在
        required_columns = ["公司名称", "简称", "主营业务", "联系方式", "总部地址", "成立时间"]
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            print(f"缺少必要的列: {missing_columns}")
            return jsonify({
                'status': 'error',
                'message': f'Excel文件缺少必要的列: {", ".join(missing_columns)}'
            }), 400
        
        # 处理数据并写入数据库
        print("开始处理数据并写入数据库")
        conn = get_db_connection()
        
        # 新增连接测试
        with conn.cursor() as test_cursor:
            test_cursor.execute("SELECT 1")
            print("数据库连接测试成功")
            
        cursor = conn.cursor()
        
        # 插入数据
        insert_count = 0
        error_count = 0
        error_messages = []
        
        # 移除对get_transaction_isolation的调用，改用更安全的事务处理
        print("开始导入数据...")
        
        for index, row in df.iterrows():
            try:
                # 处理日期格式
                found_time = None
                if pd.notna(row["成立时间"]):
                    try:
                        # 记录原始日期值以便调试
                        original_date = str(row["成立时间"]).strip()
                        print(f"处理第{index+2}行日期: '{original_date}'")
                        
                        # 将输入转换为字符串并清理
                        date_str = original_date
                        
                        # 处理年份格式（如：2019年、2011年）
                        if '年' in date_str:
                            year = date_str.replace('年', '')
                            print(f"解析到年份格式: {year}")
                            if year.isdigit() and len(year) == 4:
                                found_time = f"{year}-01-01"
                                print(f"转换为日期: {found_time}")
                            else:
                                error_messages.append(f"第{index + 2}行成立时间格式错误：{date_str}")
                                continue
                        # 处理纯数字年份（如：2019）
                        elif date_str.isdigit() and len(date_str) == 4:
                            found_time = f"{date_str}-01-01"
                            print(f"纯数字年份转换为日期: {found_time}")
                        else:
                            # 尝试解析完整日期
                            try:
                                parsed_date = pd.to_datetime(date_str)
                                found_time = parsed_date.strftime('%Y-%m-%d')
                                print(f"完整日期解析为: {found_time}")
                            except Exception as date_parse_error:
                                print(f"日期解析失败: {str(date_parse_error)}")
                                error_messages.append(f"第{index + 2}行成立时间格式错误：{date_str}")
                                continue
                    except Exception as e:
                        print(f"日期转换总体错误: {str(e)}")
                        error_messages.append(f"第{index + 2}行成立时间格式错误")
                        continue

                # 插入数据（添加调试日志）
                print(f"正在插入第{index+2}行数据, 成立时间={found_time}")
                cursor.execute("""
                    INSERT INTO suppliers 
                    (name, code, business_scope, phone, address, found_time, contact_person, status) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """, (
                    str(row["公司名称"]).strip(),
                    str(row["简称"]).strip(),
                    str(row["主营业务"]).strip(),
                    str(row["联系方式"]).strip(),
                    str(row["总部地址"]).strip(),
                    found_time,
                    "系统导入",  # 联系人默认为"系统导入"
                    "active"    # 状态默认为"active"
                ))
                
                # 新增：实时提交测试（每10条提交一次）
                if insert_count % 10 == 0 and insert_count > 0:
                    conn.commit()
                    print(f"实时提交{insert_count}条数据")
                
                # 新增：获取最后插入ID验证
                last_id = cursor.lastrowid
                print(f"插入成功，最后ID：{last_id}")
                insert_count += 1
                
            except Exception as e:
                error_count += 1
                error_message = f"第{index + 2}行数据导入失败: {str(e)}"
                print(error_message)
                error_messages.append(error_message)
                continue
        
        # 新增：事务提交验证
        try:
            conn.commit()
            print(f"事务提交成功，影响行数：{insert_count}")
        except Exception as commit_error:
            print(f"事务提交失败：{str(commit_error)}")
            raise
        
        # 新增：验证实际插入数量
        cursor.execute("SELECT COUNT(*) as total FROM suppliers")
        new_count = cursor.fetchone()['total']
        print(f"插入后数据库总记录数：{new_count}")
        
        conn.close()
        
        # 清理临时文件
        if temp_file_path and os.path.exists(temp_file_path):
            os.remove(temp_file_path)
            print("清理临时文件成功")
        
        message = f"成功导入{insert_count}条数据"
        if error_count > 0:
            message += f"，{error_count}条数据导入失败"
            if error_messages:
                message += f"，错误信息：{'; '.join(error_messages)}"
        
        print(f"导入完成: {message}")
        return jsonify({
            'code': 200,
            'data': {
                'importedCount': insert_count,
                'failedCount': error_count,
                'errors': error_messages
            },
            'message': message
        })

    except Exception as e:
        print(f"导入过程出错: {str(e)}")
        # 清理临时文件
        if temp_file_path and os.path.exists(temp_file_path):
            os.remove(temp_file_path)
            
        return jsonify({
            'code': 500,
            'message': f'导入失败: {str(e)}',
            'data': None
        }), 500

# 导出Excel文件
@app.route('/api/suppliers/export', methods=['GET'])
def export_suppliers():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 查询所有供应商数据
        cursor.execute("""
            SELECT 
                name as '公司名称',
                code as '简称',
                business_scope as '主营业务',
                phone as '联系方式',
                address as '总部地址',
                DATE_FORMAT(found_time, '%Y-%m-%d') as '成立时间'
            FROM suppliers 
            ORDER BY id DESC
        """)
        suppliers = cursor.fetchall()
        
        if not suppliers:
            return jsonify({
                'status': 'error',
                'message': '没有可导出的数据'
            }), 404
        
        # 创建DataFrame
        df = pd.DataFrame(suppliers)
        
        # 创建临时文件
        temp_file_path = os.path.join(UPLOAD_FOLDER, f'suppliers_export_{datetime.datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx')
        
        # 导出到Excel
        df.to_excel(temp_file_path, index=False, engine='openpyxl')
        
        # 读取文件内容
        with open(temp_file_path, 'rb') as f:
            file_content = f.read()
        
        # 清理临时文件
        os.remove(temp_file_path)
        
        # 设置响应头
        response = app.response_class(
            response=file_content,
            status=200,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response.headers['Content-Disposition'] = f'attachment; filename=suppliers_export_{datetime.datetime.now().strftime("%Y%m%d")}.xlsx'
        return response
        
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': f'导出失败: {str(e)}'
        }), 500

# 下载Excel模板
@app.route('/api/suppliers/template', methods=['GET'])
def download_template():
    try:
        # 创建示例数据
        template_data = {
            '公司名称': ['示例公司'],
            '简称': ['示例'],
            '主营业务': ['食品加工'],
            '联系方式': ['13800138000'],
            '总部地址': ['广东省广州市天河区'],
            '成立时间': ['2024-01-01']
        }
        
        # 创建DataFrame
        df = pd.DataFrame(template_data)
        
        # 创建临时文件
        temp_file_path = os.path.join(UPLOAD_FOLDER, 'supplier_template.xlsx')
        
        # 导出到Excel
        df.to_excel(temp_file_path, index=False, engine='openpyxl')
        
        # 读取文件内容
        with open(temp_file_path, 'rb') as f:
            file_content = f.read()
        
        # 清理临时文件
        os.remove(temp_file_path)
        
        # 设置响应头
        response = app.response_class(
            response=file_content,
            status=200,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response.headers['Content-Disposition'] = 'attachment; filename=supplier_template.xlsx'
        return response
        
    except Exception as e:
        return jsonify({
            'status': 'error',
            'message': f'下载模板失败: {str(e)}'
        }), 500

# 增加ping接口用于测试连接
@app.route('/api/ping', methods=['GET', 'OPTIONS'])
def ping():
    if request.method == 'OPTIONS':
        return '', 200
    
    # 添加缓存控制头，确保前端获取最新数据
    response = jsonify({
        'status': 'success',
        'message': 'pong',
        'timestamp': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    })
    
    response.headers['Cache-Control'] = 'no-cache, no-store, must-revalidate'
    response.headers['Pragma'] = 'no-cache'
    response.headers['Expires'] = '0'
    
    return response

# 初始化数据库
if __name__ == '__main__':
    init_database()
    # 使用0.0.0.0绑定所有网络接口
    print("启动Flask服务，监听所有网络接口，端口5000")
    app.run(debug=True, host='0.0.0.0', port=5000)